__author__ = 'Soul'

from DbConnection import *
import sqlite3
from core.Logger import *

class SqliteDbConnection(DbConnection):
    def __init__(self, dbfile):
        self.__dbfile__ = dbfile
        self.__db_name__ = dbfile
        self.__conn__ = None
        self.__type__ = 'sqlite'
        self.__cursor__ = None

    def type(self):
        return self.__type__

    # close connect
    def close(self):
        if not self.__cursor__ == None:
            self.__cursor__.close()
        if not self.__conn__ == None:
            self.__conn__.close()

    def __connect(self):
        if not self.__conn__ == None:
            return self.__conn__
        import os.path
        if os.path.exists(self.__db_name__):
            self.__conn__ = sqlite3.connect(self.__db_name__)
        else:
            return None
        return self.__conn__

    def __cursor(self):
        if self.__cursor__ == None:
            if not self.__conn__ == None:
                self.__cursor__ = self.__conn__.cursor()
        return self.__cursor__

    def do_insert(self, sql, params):
        global logger
        logger.info('[SQLITE]: Insert following sql:')
        logger.info(sql)
        import threading
        mutex = threading.Lock()
        rtrn_val = 1
        if mutex.acquire(1):
            conn = self.__connect()
            cu = self.__cursor()
            try:
                if not params or len(params):
                    cu.execute(sql, params)
                else:
                    cu.execute(sql)
                rtrn_val = cu.lastrowid
            except sqlite3.Error, e:
                logger.error('[SQLITE]: insert value failed:' + e.args[0])
                rtrn_val = 0
            finally:
                conn.commit()
                mutex.release()

        return rtrn_val

    def do_insert_many(self, sql, params):
        global logger
        logger.info('[SQLITE]: Insert many following sql:')
        logger.info(sql)
        import threading
        mutex = threading.Lock()
        rtrn_val = 1
        if mutex.acquire(1):
            conn = self.__connect()
            cu = self.__cursor()
            try:
                cu.executemany(sql, params)
                rtrn_val = cu.lastrowid
            except sqlite3.Error, e:
                logger.error('[SQLITE]: insert many value failed:' + e.args[0])
                rtrn_val = 0
            finally:
                conn.commit()
                mutex.release()

        return rtrn_val

    def do_update(self, sql, params = None):
        global logger
        logger.info('[SQLITE]: Update following sql:')
        logger.info(sql)
        import threading
        mutex = threading.Lock()
        rtrn_val = 1
        if mutex.acquire(1):
            conn = self.__connect()
            cu = self.__cursor()
            try:
                if not params or len(params):
                    cu.execute(sql, params)
                else:
                    cu.execute(sql)
                rtrn_val = cu.lastrowid
            except sqlite3.Error, e:
                logger.error('[SQLITE]: Update value failed:' + e.args[0])
                rtrn_val = 0
            finally:
                conn.commit()
                mutex.release()

        return rtrn_val

    def do_delete(self, sql, params = None):
        global logger
        logger.info('[SQLITE]: Delete following sql:')
        logger.info(sql)
        import threading
        mutex = threading.Lock()
        rtrn_val = 1
        if mutex.acquire(1):
           conn = self.__connect()
           cu = self.__cursor()
           try:
               if not params or len(params):
                    cu.execute(sql, params)
               else:
                    cu.execute(sql)
               rtrn_val = cu.rowcount
           except sqlite3.Error, e:
               logger.error('[SQLITE]: Delete value failed:' + e.args[0])
               rtrn_val = 0
           finally:
               conn.commit()
               mutex.release()

        return rtrn_val

    def select_one(self, sql, params = None):
        conn = self.__connect()
        conn.row_factory = sqlite3.Row
        cu = self.__cursor()
        try:
            if not params or len(params):
                cu.execute(sql, params)
            else:
                cu.execute(sql)
        except sqlite3.Error, e:
            logger.info(('[SQLITE]: query one data failed: %s') % (e.args[0]))
            return None
        return cu.fetchone()

    def select_all(self, sql, params = None):
        conn = self.__connect()
        conn.row_factory = sqlite3.Row
        cu = self.__cursor()
        try:
            if not params or len(params):
                cu.execute(sql, params)
            else:
                cu.execute(sql)
        except sqlite3.Error, e:
            logger.info(('[SQLITE]: query all data failed: %s') % (e.args[0]))
            return None
        return cu.fetchall()